/*
 * 本脚本用于读取形如：
 *   学号  姓名  预警级别
 */

const xlsx = require("xlsx");
const db = require('../db/connMysql');
const mysql_tool = require('../util/mysql_tool');

// 读取所有学生的信息
let fileName = '更新学生预警级别20250403.xlsx';
// 读取教师Excel文件
const workbook = xlsx.readFile(fileName);
const sheet = workbook.Sheets[workbook.SheetNames[0]];
const s = xlsx.utils.sheet_to_json(sheet);

function strip(s) {
    s = String(s);
    return s.replace(/^\s+|\s+$/gm, '');
}

let new_s = [];
for (let i = 0; i < s.length; i++) {
    let studentName = strip(s[i]['姓名']);
    let studentId = strip(s[i]['学号']);
    let level = strip(s[i]['预警级别']);
    new_s.push({
        studentName: studentName,
        studentId: studentId,
        level: level
    });
}

console.log(new_s);
console.log(new_s.length);

let error_student = [];
let no_this_student = [];
let name_no_this_student = [];

async function main() {
    // 等待数据库连接成功后执行下方代码
    if (db.conn_successful !== true) {
        setTimeout(main, 100);
        return ;
    }
    for (let i = 0; i < new_s.length; i++) {
        let item = new_s[i];
        let sql = `SELECT * FROM Students WHERE cardId = ?`;
        let data = [item.studentId];
        try {
            let result = await mysql_tool.execSql(sql, data);
            if (result.success == false) {
                console.log(result.err);
                error_student.push(item);
                continue;
            }
            if (result.result.length == 0) {
                no_this_student.push(item);
                console.log(`学号 ${item.studentId} 姓名 ${item.studentName} 不存在`);
                continue;
            }
            if (result.result[0].name != item.studentName) {
                name_no_this_student.push({
                    studentId: item.studentId,
                    studentName: item.studentName,
                    indbname: result.result[0].name
                });
                console.log(`学号 ${item.studentId} 姓名 ${item.studentName} 不匹配`);
                continue;
            }
            console.log(`学号 ${item.studentId} 姓名 ${item.studentName} 匹配`);
            sql = `UPDATE Students SET warningLevelId = (SELECT warningLevelId FROM WarningLevels WHERE name = ? LIMIT 1) WHERE cardId = ?`;
            data = [item.level, item.studentId];
            await mysql_tool.execSql(sql, data);
        } catch (e) {
            error_student.push(item);
            console.log(e);
        }
    }

    console.log('报错的学生:', error_student);
    console.log('不存在这个学生:', no_this_student);
    console.log('名字不匹配的学生:', name_no_this_student);

    console.log('开始更新预警级别');
}

main();